﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Text;

namespace YArchitech.LIB
{
	public sealed class SQLiteDataOperate
	{
		public static string DuctSpecTableName
		{
			get
			{
				return "DuctSpec";
			}
		}

		public static string PipeSpecTableName
		{
			get
			{
				return "PipeSpec";
			}
		}

		public static string WaterPropTableName
		{
			get
			{
				return "WaterProp";
			}
		}

		public static string MaterialTableName
		{
			get
			{
				return "Material";
			}
		}

		public static string ConfigFile
		{
			get
			{
				return SQLiteDataOperate.m_configFile;
			}
			set
			{
				SQLiteDataOperate.m_configFile = value;
			}
		}

		public static string DataSource
		{
			get
			{
				return SQLiteDataOperate.m_DataSource;
			}
		}

		private static string ConnectionString
		{
			get
			{
				return SQLiteDataOperate.m_ConnectionString;
			}
		}

		private static SQLiteConnection Connection
		{
			get
			{
				return SQLiteDataOperate.m_Connection;
			}
		}

		~SQLiteDataOperate()
		{
			if (SQLiteDataOperate.m_Connection != null)
			{
				SQLiteDataOperate.m_Connection.Close();
			}
		}

		public static void SetDataSource(string dataSource)
		{
			if (dataSource == SQLiteDataOperate.m_DataSource)
			{
				return;
			}
			SQLiteDataOperate.m_DataSource = dataSource;
			SQLiteDataOperate.m_ConnectionString = string.Format("Data Source={0};Pooling=true;FailIfMissing=false", dataSource);
			if (SQLiteDataOperate.m_Connection != null && SQLiteDataOperate.m_Connection.State != ConnectionState.Closed)
			{
				SQLiteDataOperate.m_Connection.Close();
			}
			SQLiteDataOperate.m_Connection = new SQLiteConnection(SQLiteDataOperate.m_ConnectionString);
			SQLiteDataOperate.m_Connection.Open();
		}

		public static SQLiteCommand NewCommand()
		{
			return new SQLiteCommand(SQLiteDataOperate.Connection);
		}

		public static DataTable ExecQuery(string sqlStr)
		{
			DataTable dataTable = new DataTable();
			DataTable result;
			using (SQLiteCommand sqliteCommand = SQLiteDataOperate.NewCommand())
			{
				try
				{
					sqliteCommand.CommandText = sqlStr;
					sqliteCommand.CommandType = CommandType.Text;
					IDataReader reader = sqliteCommand.ExecuteReader();
					dataTable.Load(reader);
					result = dataTable;
				}
				catch (SQLiteException ex)
				{
					string message = ex.Message;
					result = null;
				}
			}
			return result;
		}

		public static bool ExecuteCommand(string sqlStr)
		{
			bool result;
			using (SQLiteCommand sqliteCommand = SQLiteDataOperate.NewCommand())
			{
				try
				{
					sqliteCommand.CommandText = sqlStr;
					sqliteCommand.CommandType = CommandType.Text;
					if (sqliteCommand.ExecuteNonQuery() > 0)
					{
						result = true;
					}
					else
					{
						result = false;
					}
				}
				catch (Exception ex)
				{
					string message = ex.Message;
					result = false;
				}
			}
			return result;
		}

		internal static int GetRecordCount(string sqlQueryString)
		{
			int result;
			using (SQLiteCommand sqliteCommand = SQLiteDataOperate.NewCommand())
			{
				try
				{
					sqliteCommand.CommandText = sqlQueryString;
					SQLiteDataReader sqliteDataReader = sqliteCommand.ExecuteReader();
					if (sqliteDataReader.Read())
					{
						result = sqliteDataReader.GetInt32(0);
					}
					else
					{
						result = 0;
					}
				}
				catch (SQLiteException ex)
				{
					string message = ex.Message;
					result = 0;
				}
			}
			return result;
		}

		internal static int GetTableRecordCount(string tableName)
		{
			return SQLiteDataOperate.GetRecordCount(string.Format("SELECT count(*)  FROM {0}", tableName));
		}

		internal static bool DeleteTableRecordById(string tableName, int id)
		{
			bool result;
			try
			{
				result = (new SQLiteCommand(SQLiteDataOperate.Connection)
				{
					CommandText = string.Format("DELETE FROM [{0}] where [Id]={1}", tableName, id)
				}.ExecuteNonQuery() > 0);
			}
			catch (SQLiteException ex)
			{
				string message = ex.Message;
				result = false;
			}
			return result;
		}

		internal static bool DeleteTableRecordByIds(string tableName, int[] ids)
		{
			StringBuilder stringBuilder = new StringBuilder("-1");
			for (int i = 0; i < ids.Length; i++)
			{
				stringBuilder.Append(string.Format(",{0}", ids[i].ToString()));
			}
			bool result;
			try
			{
				result = (new SQLiteCommand(SQLiteDataOperate.Connection)
				{
					CommandText = string.Concat(new string[]
					{
						"DELETE FROM ",
						tableName,
						"where [Id]=in(",
						stringBuilder.ToString(),
						")"
					})
				}.ExecuteNonQuery() > 0);
			}
			catch (SQLiteException ex)
			{
				string message = ex.Message;
				result = false;
			}
			return result;
		}

		internal static int GetNewId(SQLiteCommand cmd)
		{
			int result = -1;
			try
			{
				cmd.CommandText = "select last_insert_rowid()";
				SQLiteDataReader sqliteDataReader = cmd.ExecuteReader();
				if (sqliteDataReader.Read())
				{
					result = Convert.ToInt32(sqliteDataReader[0]);
				}
				sqliteDataReader.Close();
			}
			catch (SQLiteException ex)
			{
				string message = ex.Message;
			}
			return result;
		}

		private static string m_configFile = string.Empty;

		private static string m_DataSource = string.Empty;

		private static string m_ConnectionString = string.Empty;

		private static SQLiteConnection m_Connection = null;
	}
}
